'''
select blocklist.blockname,welllist.wellname
from wellbelong
LEFT JOIN blocklist
on wellbelong.blockid=blocklist.blockid
LEFT JOIN welllist
on wellbelong.wellid=welllist.wellid;


'''
import pandas as pd
import pymysql as pmq
import cx_Oracle as oracle
import os
############################## class action ###############################

############################## class data ###############################

# mysql cursor class

class pmqcur():
    #
    def __init__(self,info=None,**kwargs):
        self.conn = None
        self.cur = None
        self.info=info
        if self.info:
            self.connect(self.info)

    def connect(self, info=None):
        if info:
            self.info=info
        
        self.conn = pmq.connect(
            host=self.info[0],
            port=self.info[1],
            user=self.info[2],
            password=self.info[3]
        )
        self.cur = self.conn.cursor()


    def execute(self, sql, tup=None):
        self.ping()

        self.cur.execute(sql,tup)
        des = pd.DataFrame(self.cur.description).iloc[:, 0] 
        dat = pd.DataFrame(self.cur.fetchall(),columns=des)
        dat.columns = des
        return dat

    def commit(self):
        self.conn.commit()

    def execute0(self, sql, tup=None):
        self.ping()

        if tup == None:
            self.cur.execute(sql)
        else:
            self.cur.execute(sql, tup)
            self.conn.commit()
            
    def tryexecute0(self,sql,tup=None):
        self.ping()
        if tup == None:
            self.cur.execute(sql)
        else:
            self.cur.execute(sql, tup)
            self.conn.commit()

    def close(self):
        self.conn.close()

    def ping(self):
        self.conn.ping(True)

# data class: connection info

class dataconnectinfo():
    def __init__(self):
        pass

    def update(self, info):
        self.host = info[0]
        self.port = info[1]
        self.user = info[2]
        self.password = info[3]
        pass

    def info(self):  # return list4
        return [self.host, self.port, self.user, self.password]

    pass

class oracur():
    #
    def __init__(self, info=None,**kwargs):
        self.conn = None
        self.cur = None
        self.info=info
        if self.info:
            self.connect(self.info)

    def connect(self, info=None):
        os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
        
        if info:
            self.info=info
        
        self.conn = oracle.connect(
            self.info[0],
            self.info[1],
            self.info[2]
        )
        self.cur = self.conn.cursor()
        pass

    def execute(self, sql, dict=None):
        self.ping()

        self.cur.execute(sql,dict)
        des = pd.DataFrame(self.cur.description).iloc[:, 0] 
        dat = pd.DataFrame(self.cur.fetchall(),columns=des)
        dat.columns = des
        return dat

    def commit(self):
        self.conn.commit()

    def close(self):
        self.conn.close()

    def ping(self):
        self.connect()
        
class dataglobal():
    #
    def __init__(self):
        self.connectedflag = False
        self.connectinfo = dataconnectinfo()
        self.pmqcur = pmqcur()
        self.oracur = oracur(['XINXI', 'XINXI', '10.76.19.33:2520/oraunix'])
        pass

    def getdbtable(self):
        sqldb = 'show databases;'
        self.pmqcur.cur.execute(sqldb)
        db = self.pmqcur.cur.fetchall()
        table = []
        for i in db:
            sqltable = 'show tables from '+i[0]+';'
            table.append(self.pmqcur.cur.fetchall())
        return db, table

    def getheader(self, db, table):
        sql = 'show columns from '+db+'.'+table+';'
        self.pmqcur.cur.execute(sql)
        head = self.pmqcur.cur.fetchall()
        return head

    def blockname2id(self, name):
        sql = 'select id_block from staticinfo.def_block where name_block='+name+';'
        self.pmqcur.cur.execute(sql)
        return self.pmqcur.cur.fetchall()

    def wellname2id(self, name):
        sql = 'select id_well from staticinfo.def_well where name_well='+name+';'
        self.pmqcur.cur.execute(sql)
        return self.pmqcur.cur.fetchall()

    def getwellinfo(self, name):
        idwell = self.wellname2id(name)
        print(idwell)
        sql = 'select * from staticinfo.event_well where id_well='+idwell+';'
        self.pmqcur.cur.execute(sql)
        return self.pmqcur.cur.fetchall()

    pass

############################## functions  ###############################

def getsql1line(db, a):
    ret = "use "+db+';'
    for i in a:
        ret = ret+i+" "
    ret = ret-"\b"+";"
    return ret


def chklist(s, l):
    ret = True
    for i in s:
        ret = ret and i in l
    return ret


def fkconvert(col, table):
    n = len(col)
    out = []*n
    for i in range(n):
        for link in table:
            if link[0] == col[i]:
                out[i] = link[1]
    return out
